Please help debug this ASP.Net [VB] code. Trying to write to text file from SQL Server DB.

Posted by NJTechGuy on Stack Overflow See other posts from Stack Overflow or by NJTechGuy
Published on 2010-03-21T02:13:22Z Indexed on 2010/03/21 2:21 UTC
Read the original article Hit count: 329

Filed under:

.NET

|

sql-server

|

text-file

|

vb.net

I am a PHP programmer. I have no .Net coding experience (last seen it 4 years ago). Not interested in code-behind model since this is a quick temporary hack.

What I am trying to do is generate an output.txt file whenever the user submits new data. So an output.txt file if exists should be replaced with the new one.

I want to write data in this format :

123|Java Programmer|2010-01-01|2010-02-03
124|VB Programmer|2010-01-01|2010-02-03
125|.Net Programmer|2010-01-01|2010-02-03

I don't know VB, so not sure about string manipulations. Hope a kind soul can help me with this. I will be grateful to you. Thank you :)

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="vb" runat="server">
  sub Page_Load(sender as Object, e as EventArgs)

  Dim sqlConn As New SqlConnection("Data Source=winsqlus04.1and1.com;Initial Catalog=db28765269;User Id=dbo2765469;Password=ByhgstfH;") 
  Dim myCommand As SqlCommand
  Dim dr As SqlDataReader

  Dim FILENAME as String = Server.MapPath("Output4.txt")

    Dim objStreamWriter as StreamWriter
   ' If Len(Dir$(FILENAME)) > 0 Then Kill(FILENAME)
    objStreamWriter = File.AppendText(FILENAME)

   Try
   sqlConn.Open()
   'opening the connection
   myCommand = New SqlCommand("SELECT  id, title, CONVERT(varchar(10), expirydate, 120) AS [expirydate],CONVERT(varchar(10), creationdate, 120) AS [createdate] from tblContact where flag = 0 AND ACTIVE = 1", sqlConn)
  'executing the command and assigning it to connection 
  dr = myCommand.ExecuteReader()

  While dr.Read()

    objStreamWriter.WriteLine("JobID: " & dr(0).ToString())
    objStreamWriter.WriteLine("JobID: " & dr(2).ToString())
    objStreamWriter.WriteLine("JobID: " & dr(3).ToString())

  End While
  dr.Close()
  sqlConn.Close()
  Catch x As Exception
  End Try

    objStreamWriter.Close()

    Dim objStreamReader as StreamReader
    objStreamReader = File.OpenText(FILENAME)

    Dim contents as String = objStreamReader.ReadToEnd()

    lblNicerOutput.Text = contents.Replace(vbCrLf, "<br>")

    objStreamReader.Close()
  end sub
</script>

<asp:label runat="server" id="lblNicerOutput" Font-Name="Verdana" />

Developer IT